Skip Headers

Oracle® Database Application Developer's Guide - Rules Manager and Expression Filter 10g Release 2 (10.2) Part Number B14288-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 13 Expressions with Spatial Predicates

Note: The Oracle Spatial or the Locator components must be installed to use spatial predicates in stored expressions.

The expressions stored in a column of a table may contain spatial predicates defined on `SDO_GEOMERTY` attributes. This section describes an application for spatial predicates using the Car4Sale example introduced in Chapter 10. For this purpose, the information published for each car going on sale includes a `Location` attribute in addition to the `Model`, `Price`, `Mileage`, and `Year` attributes. The `Location` attribute contains geographical coordinates for the vehicle's location, as an instance of the `SDO_GEOMETRY` data type.

Using the `Location` attribute, the consumer interested in a vehicle can restrict the search only to the vehicles that are within a specified distance, say half a mile, of his own location. This can be specified using the following spatial predicate involving the `SDO_WITHIN_DISTANCE` operator:

    SDO_WITHIN_DISTANCE(
      Location,
      SDO_GEOMETRY(
        2001, 8307,
        SDO_POINT_TYPE(-77.03644, 37.89868, NULL), NULL, NULL
      ) ,
      'distance=0.5 units=mile'
    ) = 'TRUE'

Note that spatial predicates are efficiently evaluated with the help of spatial indexes. Chapter13 and Chapter13 will describe how to specify spatial predicates in arbitrary expressions and how to ensure the predicates are evaluated using appropriate spatial indexes.

## Using Spatial Predicates in Expressions

Using the Oracle supplied `SDO_GEOMETRY` data type, users can specify spatial predicates on instances of spatial geometries within a standard SQL `WHERE` clause of a query. These predicates use operators such as `SDO_WITHIN_DISTANCE` and `SDO_RELATE` on an instance of `SDO_GEOMETRY` data type to relate two spatial geometries in a specific way. For more information, see Oracle Spatial User's Guide and Reference.

To allow spatial predicates in an expression set, the corresponding attribute set should be created with an attribute of `MDSYS.SDO_GEOMETRY` data type as shown in the following example:

    CREATE OR REPLACE TYPE Car4Sale AS OBJECT
                       (Model    VARCHAR2(20),
                        Year     NUMBER,
                        Price    NUMBER,
                        Mileage  NUMBER,
                        Location MDSYS.SDO_GEOMETRY);
    /
    BEGIN
      dbms_expfil.create_attribute_set (attr_set => 'Car4Sale',
                                        from_type => 'YES');
    END;
    /

In order to specify predicates on the spatial attribute and index them for efficiency, the geometry metadata describing the dimension, lower and upper bounds, and tolerance in each dimension should associated with each spatial geometry attribute in the attribute set. This metadata information can be inserted into the `USER_SDO_GEOM_METADATA` view using the attribute set name in the place of the table name. For more information on the `USER_SDO_GEOM_METADATA` view and its semantics, see Oracle Spatial User's Guide and Reference.

    INSERT INTO user_sdo_geom_metadata VALUES ('CAR4SALE','LOCATION',
         mdsys.sdo_dim_array(
              mdsys.sdo_dim_element('X',  -180, 180, 0.5),
              mdsys.sdo_dim_element('Y',  -90, 90, 0.5)), 8307);

The expression set using the attribute set with one or more `SDO_GEOMETRY` attributes can include predicates on such attributes using `SDO_WITHIN_DISTANCE` or `SDO_RELATE` operators, as shown in the following examples:

    Model = 'Taurus' and Price < 15000 and Mileage < 25000 and
      SDO_WITHIN_DISTANCE (Location,
          SDO_GEOMETRY(2001, 8307,
            SDO_POINT_TYPE(-77.03644, 37.89868, NULL), NULL, NULL),
          'distance=0.5 units=mile') = 'TRUE'

    Model = 'Taurus' and Price < 15000 and Mileage < 25000 and
      SDO_RELATE (Location,
          SDO_GEOMETRY(2001, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
             SDO_ORDINATE_ARRAY(-77.03644, 37.89868, -75, 39),
          'mask=anyinteract') = 'TRUE'

Note that unlike in the case of expressions with purely non-spatial predicates, expressions with spatial predicates cannot be evaluated when an Expression Filter index is not defined for the expression set. Once an Expression Filter index is created on the column storing the expressions, expressions with spatial predicates can be processed for a data item by passing an instance of `SDO_GEOMETRY` data type for the `Location` attribute, along with other attribute values, to the `EVALUATE` operator.

    SELECT * FROM Consumer WHERE
      EVALUATE (Interest,
        sys.anyData.convertObject(
           Car4Sale('Mustang', 2002, 20000, 250000,
                SDO_GEOMETRY(2001, 8307,
                sdo_point_type(-77.03644, 38.9059284, null), null, null)))
      ) = 1;

The previous query identifies all the rows with expressions that are true based on their spatial and not-spatial predicates.

## Indexing Spatial Predicates

The spatial predicates in the stored expressions are processed using some custom spatial indexes created on the geometries specified in the spatial predicates. These spatial indexes are automatically created when the Expression Filter index is created on the column storing expressions. The expressions with spatial predicates cannot be processed in the absence of these spatial indexes and hence an Expression Filter index is always required to evaluate such expressions.

When an Expression Filter index is defined on an expression column, the spatial attributes in corresponding attribute set are all considered as indexed predicate groups. The predicate table has columns of `SDO_GEOMETRY` type for each of these attributes and spatial indexes are created on these columns. The values stored in an `SDO_GEOMETRY` column of the predicate table are computed based on the values specified in the spatial predicates involving corresponding attribute.

When the expressions are evaluated for a data item, the spatial indexes created on the geometry column in combination with bitmap indexes created for the other indexed predicate groups filter out the expressions that are false based on all indexed predicate groups. The expressions remaining in the working set are further evaluated based on the stored predicate groups and sparse predicates to identify all the expressions that are true for the given data item.